﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace Test.Date
{

    public class MSSQL
    {
        //3、将当前连接存储下来，创建一个字段
        private SqlConnection _conn;
        

        public void Connect()
        {
            //1、SqlConnection()  与SQL建立连接
            SqlConnection conn = new SqlConnection("Data Source " + "= SAN-PC\\WINCCPLUSMIG2014; User ID = sa; Password = sa; Initial Catalog = Test;");
            //2、注意 Data Source = SAN - PC\\WINCCPLUSMIG2014    填本机实际的服务器名称 ，而不是localhost
            this._conn = conn;
            try
            {
                conn.Open();
               
            }
            catch (Exception ex)
            {
                
            }
                   
        }
        
        //4、查询
        public void Query_write(string sql)
        {

            //写入数据到数据库
            //SqlCommand cmd = this._conn.CreateCommand();//方式一
            // SqlCommand cmd2 = new SqlCommand(sql, this._conn);//方式二
            SqlCommand cmd = new SqlCommand(sql, this._conn);
            try
            {
                cmd.ExecuteNonQuery();
                //Console.WriteLine("执行成功 " + sql);

                
            }
           catch(Exception ex)  
            {
              //  Console.WriteLine("执行失败 " + ex.Message);
            }
        }


        //5、读取数据库   通过适配器SqlDataAdapter
        public DataSet sqldataadapter(string sql)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(sql, this._conn);

            //6、DataSet数据集
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds;
        }

        //6. 读取数据库   通过适配器SqlDataReader
        public void sqldatareader(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql,this._conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                for (int i = 0; i < reader.GetColumnSchema().Count; i++)//reader.GetColumnSchema();//获取列的结构，返回一个只读集合
                {
                    Console.WriteLine(reader.GetValue(i));
                }
                
                
            }
             reader.Close();
        }

        public void 删除表(string sql)
        {
            Query_write($"DROP TABLE {sql};");
        }

        public void 添加表(string table ,string key,string value)
        {
            Query_write($"CREATE TABLE {table} ( {key} VARCHAR(50) NULL,{value} VARCHAR(50) NULL)");
            /*12、创建表
            CREATE TABLE flow34 ( Code VARCHAR(50) NULL,Name VARCHAR(50) NULL)
             
             */
        }

    }


}
